package pandora.data.mysql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

import pandora.connection.ConnectionManager;
import pandora.data.IAccountDAO;
import pandora.data.IStoreDAO;
import pandora.dto.StoreDTO;

public class JDBCStoreDAO implements IStoreDAO {

	private Connection conn;
	private IAccountDAO account;
	
	public JDBCStoreDAO() {
		conn = ConnectionManager.getInstance().checkOut();
		account = new JDBCAccountDAO();
	}

	protected void finalize() {
		ConnectionManager.getInstance().checkIn(conn);
	}

	public List<StoreDTO> getAllStores() {
		List<StoreDTO> stores;
		stores = new LinkedList<StoreDTO>();
		ResultSet rs = null;
		Statement stmt = null;

		try {
			stmt = conn.createStatement();

			String sql = "SELECT S.* FROM Stores S;";
			rs = stmt.executeQuery(sql);

			while (rs.next()) {
				StoreDTO s = new StoreDTO();
				s.setAccount(account.selectAccountByOID(rs.getString("accountOID")));
				s.setStoreName(rs.getString("storeName"));
				s.setStoreNIF(rs.getString("NIF"));
				s.setStoreSite(rs.getString("storeSite"));
				s.setStoreAddress(rs.getString("storeAddress"));

				stores.add(s);
			}
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
				System.out.println("RESULTSET CLOSING ERROR OR STATEMENT CLOSING ERROR");
			}
		}

		return stores;
	}

	public StoreDTO getStoreByNIF(String nif) {

		StoreDTO s;
		s = new StoreDTO();
		ResultSet rs = null;
		Statement stmt = null;

		try {
			stmt = conn.createStatement();

			String sql = "select * from Stores where nif = '" + nif + "'";
			rs = stmt.executeQuery(sql);

			rs.next();

			s.setStoreName(rs.getString("storeName"));
			s.setStoreNIF(rs.getString("NIF"));
			s.setStoreSite(rs.getString("storeSite"));
			s.setStoreDetails(rs.getString("storeDetails"));
			s.setStoreAddress(rs.getString("storeAddress"));

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());

		} finally {
			
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
				System.out
						.println("RESULTSET CLOSING ERROR OR STATEMENT CLOSING ERROR");
			}
		}

		return s;
	}

	public String selectStoreOIDByNIF(String storeNIF) {
		PreparedStatement stmt = null;
		ResultSet result = null;
		String oid = null;
		String sql = "SELECT S.* FROM Stores S WHERE (S.NIF = ?) ";

		try {
			stmt = conn.prepareStatement(sql);

			stmt.setString(1, storeNIF);
			result = stmt.executeQuery();

			if (result.next()) {
				oid = result.getString("OID");
			}
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			try {
				if (result != null) {
					result.close();
				}
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {
			}
		}
		return oid;
	}

	
	public boolean isStore(String usr) {
		boolean res = false;

		IAccountDAO accountDAO = new JDBCAccountDAO();

		Statement stmt = null;
		ResultSet rs = null;

		try {

			String accountOID = accountDAO.selectAccountOIDByUser(usr);

			stmt = conn.createStatement();

			String sql = "select * from Stores where accountOID =" + accountOID;

			rs = stmt.executeQuery(sql);

			while (rs.next()) {
				res = true;
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {
			}
		}

		return res;
	}

	//DCC
	public StoreDTO selectStoreByAccount(String usr) {
		StoreDTO s = new StoreDTO();
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		String sql = "SELECT S.* FROM Stores S, Accounts A "+
						"WHERE (A.user = ?) "+
						"AND (A.OID = S.accountOID);";
		
		try{
			stmt = conn.prepareStatement(sql);
			
	        stmt.setString(1, usr);

	        rs = stmt.executeQuery();
			
	        if(rs.next()){
	        	s.setStoreName(rs.getString("storeName"));
				s.setStoreNIF(rs.getString("NIF"));
				s.setStoreSite(rs.getString("storeSite"));
				s.setStoreDetails(rs.getString("storeDetails"));
				s.setStoreAddress(rs.getString("storeAddress"));
	        }			
		}catch(SQLException e){
			e.printStackTrace();
		} finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
		return s;
	}
	//Fin DCC

	//DFAJ
	public StoreDTO getStoreByOID(String soid) {
		StoreDTO s = new StoreDTO();
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		String sql = "SELECT * FROM Stores WHERE (OID = ?);";
		
		try{
			stmt = conn.prepareStatement(sql);
	        stmt.setString(1, soid);
	        rs = stmt.executeQuery();
			
	        if(rs.next()){
	        	s.setStoreName(rs.getString("storeName"));
				s.setStoreNIF(rs.getString("NIF"));
				s.setStoreSite(rs.getString("storeSite"));
				s.setStoreDetails(rs.getString("storeDetails"));
				s.setStoreAddress(rs.getString("storeAddress"));
	        }			
		}catch(SQLException e){
			e.printStackTrace();
		} finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
		return s;
	}
	//DFAJ
	
	//AVC
}
